﻿--use PolePosition
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'up_UtilityAssetXMissionStructuresPoles_SelectForGrid')
	DROP PROCEDURE up_UtilityAssetXMissionStructuresPoles_SelectForGrid
GO

CREATE PROCEDURE up_UtilityAssetXMissionStructuresPoles_SelectForGrid
	 @UtilityFk int 
	,@PageSize int = 100
	,@PageNumber int = 1
	,@SearchByType varchar(50) = null
	,@SearchByText varchar(50) = null
AS

SET NOCOUNT ON	

DECLARE @StartRowNumber int
DECLARE @EndRowNumber int

SET @StartRowNumber = ((@PageNumber - 1) * @PageSize) + 1
SET @EndRowNumber = @PageNumber * @PageSize

DECLARE @table table
(
 	 RowNumber int
	,TotalCount int
	,Utility_Asset_XMissionStructures_PoleId int
	,Utility_Asset_XMissionStructureFK int
	,XMissionStructures_PoleTopTypeFk int
	,XMissionStructures_PoleSpeciesFk int
	,XMissionStructures_PoleTreatmentFk int
	,Year int
	,XMissionStructures_PoleClassFk int
	,Latitude float
	,Longitude float
	,Altitude float
	,SatelliteCount int
	,SatelliteTime datetime
	,SolutionAvailable bit
	,AID nvarchar(100)
	,LVisit int
	,Number nvarchar(10)
	,Length float
	,LVisitor nvarchar(100)
	,MapFk int
	,SectionFk int
	,[FriendlyName] varchar(255)
)
	
INSERT @table
(
	 [RowNumber]
	,[TotalCount]
	,[Utility_Asset_XMissionStructures_PoleId]
	,[Utility_Asset_XMissionStructureFK]
	,[XMissionStructures_PoleTopTypeFk]
	,[XMissionStructures_PoleSpeciesFk]
	,[XMissionStructures_PoleTreatmentFk]
	,[Year]
	,[XMissionStructures_PoleClassFk]
	,[Latitude]
	,[Longitude]
	,[Altitude]
	,[SatelliteCount]
	,[SatelliteTime]
	,[SolutionAvailable]
	,[AID]
	,[LVisit]
	,[Number]
	,[Length]
	,[LVisitor]
	,MapFk
	,SectionFk	
	,[FriendlyName]
)
SELECT 
*
FROM 
(
	SELECT 
		 ROW_NUMBER() OVER (ORDER BY Pole.Utility_Asset_XMissionStructures_PoleId) AS RowNumber
		,COUNT(Pole.Utility_Asset_XMissionStructures_PoleId) OVER (PARTITION BY NULL) AS TotalCount
		,Pole.[Utility_Asset_XMissionStructures_PoleId]
		,Pole.[Utility_Asset_XMissionStructureFK]
		,Pole.[XMissionStructures_PoleTopTypeFk]
		,Pole.[XMissionStructures_PoleSpeciesFk]
		,Pole.[XMissionStructures_PoleTreatmentFk]
		,Pole.[Year]
		,Pole.[XMissionStructures_PoleClassFk]
		,Pole.[Latitude]
		,Pole.[Longitude]
		,Pole.[Altitude]
		,Pole.[SatelliteCount]
		,Pole.[SatelliteTime]
		,Pole.[SolutionAvailable]
		,Pole.[AID]
		,Pole.[LVisit]
		,Pole.[Number]
		,Pole.[Length]
		,Pole.[LVisitor]
		,Pole.MapFk
		,Pole.SectionFk
		,Pole.[FriendlyName]
	FROM v_tb_Utility_Asset_XMissionStructures_Poles Pole
	INNER JOIN tb_Utility_Asset_XMissionStructures Structure
		ON Structure.Utility_Asset_XMissionStructureId = Pole.Utility_Asset_XMissionStructureFK
	WHERE
		Structure.[UtilityFK] = @UtilityFk
) AS Data
WHERE RowNumber BETWEEN @StartRowNumber AND @EndRowNumber


-- primary select
SELECT * FROM @table



GO